#! /usr/bin/env bash

sqlite cs147organizer.db <<SQL_ENTRY_TAG_1
CREATE TABLE users(
user_id INTEGER NOT NULL,
username TEXT UNIQUE,
time_created TEXT,
email TEXT,
tel TEXT,
mobile TEXT,
avatar TEXT,
color TEXT,
average_pages_per_minute REAL,
average_problems_per_minute REAL,
deleted INTEGER NOT NULL,
PRIMARY KEY(user_id) );
SQL_ENTRY_TAG_1

sqlite cs147organizer.db <<SQL_ENTRY_TAG_2
CREATE TABLE passwords(
user_id INTEGER NOT NULL,
password TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(user_id),
FOREIGN KEY(user_id) REFERENCES users(user_id) );
SQL_ENTRY_TAG_2

sqlite cs147organizer.db <<SQL_ENTRY_TAG_3
CREATE TABLE tasks(
task_id INTEGER NOT NULL,
task_name TEXT,
time_created TEXT,
due_time_from TEXT,
due_time_to TEXT,
user_id INTEGER,
project_id INTEGER,
assigned_to INTEGER,
help_required INTEGER,
completed INTEGER,
disabled INTEGER,
pages INTEGER,
problems INTEGER,
pages_remaining INTEGER,
problems_remaining INTEGER,
time_to_complete_in_minutes INTEGER,
deleted INTEGER NOT NULL,
PRIMARY KEY(task_id) );
SQL_ENTRY_TAG_3

sqlite cs147organizer.db <<SQL_ENTRY_TAG_5
CREATE TABLE task_dependencies(
task_id INTEGER NOT NULL,
dependency_id INTEGER NOT NULL,
deleted INTEGER NOT NULL,
PRIMARY KEY(task_id,dependency_id) );
SQL_ENTRY_TAG_5

sqlite cs147organizer.db <<SQL_ENTRY_TAG_4
CREATE TABLE projects(
project_id INTEGER NOT NULL,
project_name TEXT,
time_created TEXT,
user_id INTEGER,
description TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(project_id) );
SQL_ENTRY_TAG_4

sqlite cs147organizer.db <<SQL_ENTRY_TAG_5
CREATE TABLE project_membership(
project_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
time_joined TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(project_id,user_id),
FOREIGN KEY(project_id) REFERENCES projects(project_id),
FOREIGN KEY(user_id) REFERENCES users(user_id) );
SQL_ENTRY_TAG_5

sqlite cs147organizer.db <<SQL_ENTRY_TAG_6
CREATE TABLE task_comments(
comment_id INTEGER NOT NULL,
task_id INTEGER NOT NULL,
user_id INTEGER,
comment TEXT,
time TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(comment_id),
FOREIGN KEY(user_id) REFERENCES users(user_id) );
SQL_ENTRY_TAG_6

sqlite cs147organizer.db <<SQL_ENTRY_TAG_7
CREATE TABLE project_invitations(
project_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
inviter_id INTEGER,
message TEXT,
time TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(project_id,user_id),
FOREIGN KEY(project_id) REFERENCES projects(project_id),
FOREIGN KEY(user_id) REFERENCES users(user_id) );
SQL_ENTRY_TAG_7

sqlite cs147organizer.db <<SQL_ENTRY_TAG_8
CREATE TABLE contact_list(
user_id INTEGER NOT NULL,
contact_user_id INTEGER NOT NULL,
time_connected TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(user_id,contact_user_id) );
SQL_ENTRY_TAG_8

sqlite cs147organizer.db <<SQL_ENTRY_TAG_9
CREATE TABLE meetings(
meeting_id INTEGER NOT NULL,
meeting_name TEXT,
user_id INTEGER,
duration_in_minutes INTEGER,
location TEXT,
meeting_time TEXT,
meeting_description TEXT,
meeting_time_suggestion TEXT,
deleted INTEGER NOT NULL,
PRIMARY KEY(meeting_id) );
SQL_ENTRY_TAG_9

sqlite cs147organizer.db <<SQL_ENTRY_TAG_10
CREATE TABLE meeting_membership(
meeting_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
deleted INTEGER NOT NULL,
PRIMARY KEY(meeting_id,user_id),
FOREIGN KEY(meeting_id) REFERENCES meetings(meeting_id),
FOREIGN KEY(user_id) REFERENCES users(user_id) );
SQL_ENTRY_TAG_10
